with t_network as (--网点资料
    select code as network_code,
name,
city_id,
city_desc,
area_id,
area_desc,
regional_id,
regional_desc,
area_info_id,
area_info_desc,
provider_id,
provider_desc,
opening_time
    from (
             select t_network.*,
  case when type_id = 335 then 1 when type_id = 336 and is_entrepot = 1 then 2 else 3 end station_type,--1 中心 2 集散
                    row_number() over (partition by t_network.code order by t_network.update_time desc) bill_rank
             from jms_dim.dim_sys_network_detail_hi t_network
         ) t where bill_rank = 1 and station_type= 3  and is_delete=1
),

detail_shadan as(
         SELECT
         network_code,
         area_id,
         city_id,
         data_type,
         if(t1.effect_time_start is null,
         '1999-01-01 01:01:01', t1.effect_time_start) AS effect_time_start ,
          if(t1.effect_time_end is null, '9999-01-01 01:01:01', t1.effect_time_end) AS effect_time_end
        FROM jms_dim.dim_yl_oms_order_interceptor_hi t1
        WHERE data_type in ('area', 'network')
                AND is_delete = 0 -- 未删除
                AND is_enable = 1 -- 已启用
                AND (effect_time_type = 0
               or( DATE_FORMAT('{{ execution_date | cst_ds }}','yyyy-MM-dd') >= DATE_FORMAT(effect_time_start,'yyyy-MM-dd')
                AND DATE_FORMAT( '{{ execution_date | cst_ds }}','yyyy-MM-dd') <= DATE_FORMAT(effect_time_end,'yyyy-MM-dd'))
               )
               and DATE_FORMAT( create_time,'yyyy-MM-dd') <= DATE_FORMAT( '{{ execution_date | cst_ds }}','yyyy-MM-dd')

        union all
        select
        network_code ,
        area_id,
        city_id,
        data_type,
        effect_time_start,
        effect_time_end
        FROM
        (
        SELECT
        network_code ,
        area_id,
        city_id,
        data_type,
         if(t1.effect_time_start is null,
         '1999-01-01 01:01:01', t1.effect_time_start) AS effect_time_start ,
          nvl(if (t1.EXPECTED_EFFECT_TIME_END is not null, date_add(t1.EXPECTED_EFFECT_TIME_END,5), date_add(t1.effect_time_end,5)),'9999-01-01 01:01:01') AS effect_time_end
        FROM jms_dim.dim_yl_oms_order_interceptor_hi t1
        WHERE data_type in ('area', 'network')
                AND is_delete = 0 -- 未删除
                AND (is_enable = 0 -- 未启用
               or ( EFFECT_TIME_TYPE = 1 and (DATE_FORMAT('{{ execution_date | cst_ds }}','yyyy-MM-dd') < DATE_FORMAT(effect_time_start,'yyyy-MM-dd')
                or DATE_FORMAT( '{{ execution_date | cst_ds }}','yyyy-MM-dd') > DATE_FORMAT(effect_time_end,'yyyy-MM-dd')))
               )
                and (EXPECTED_EFFECT_TIME_END is not null or effect_time_end is not null)
                and DATE_FORMAT( create_time,'yyyy-MM-dd') <= DATE_FORMAT( '{{ execution_date | cst_ds }}','yyyy-MM-dd')
                )t  where DATE_FORMAT('{{ execution_date | cst_ds }}','yyyy-MM-dd') <= DATE_FORMAT(effect_time_end,'yyyy-MM-dd')

),

network_shadan as(
select
network_code
from
(
select
b.network_code
from
detail_shadan a  join t_network b
on  a.area_id   = b.area_id  and a.area_id is not null and a.data_type = 'area'
union ALL
select
c.network_code
from
detail_shadan a join t_network c
on  a.city_id   = c.city_id  and a.area_id is  null and a.city_id is not null and a.data_type = 'area'
union all
select
network_code
FROM
detail_shadan
where data_type='network'
) t group by network_code
),

network_abnormal as(
       SELECT involved_org_code AS network_code
           FROM jms_dim.dim_abnormal_event_report_base_hi
           WHERE DATE_FORMAT( '{{ execution_date | cst_ds }}','yyyy-MM-dd') <= DATE_FORMAT(end_time,'yyyy-MM-dd')
            and  DATE_FORMAT( '{{ execution_date | cst_ds }}','yyyy-MM-dd') >= DATE_FORMAT(happen_time,'yyyy-MM-dd')
           group by involved_org_code
),
new_data as(
SELECT
f.input_date,
f.network_code,
b.name as network_name,
b.regional_id,
b.regional_desc,
b.area_info_id,
b.area_info_desc,
b.provider_id,
b.provider_desc,
b.city_id,
b.city_desc,
b.area_id,
b.area_desc,
f.is_shaidan,
f.is_delay,
f.is_new_open,
f.is_special_report,
f.create_time
from
(
SELECT
network_code,
'{{ execution_date | cst_ds }}' as input_date,
max(type1) as is_shaidan,
max(type2) as is_delay,
max(type3) as is_new_open,
max(type4) as is_special_report,
CURRENT_TIMESTAMP as create_time,
'{{ execution_date | cst_ds }}'  as dt
from
(
SELECT
network_code,
1 as type1,
0 as type2,
0 as type3,
0 as type4

from
network_shadan

union all

SELECT
network_code,
0 as type1,
1 as type2,
0 as type3,
0 as type4
from
network_abnormal

union all

SELECT
network_code,
0 as type1,
0 as type2,
0 as type3,
1 as type4
from
jms_dim.dim_tab_outlets_abnormal_config_base_hi
where is_del=0
and DATE_FORMAT( '{{ execution_date | cst_ds }}','yyyy-MM-dd') <= DATE_FORMAT(effect_end_time,'yyyy-MM-dd')
and  DATE_FORMAT( '{{ execution_date | cst_ds }}','yyyy-MM-dd') >= DATE_FORMAT(effect_start_time,'yyyy-MM-dd')
group by network_code

union ALL
select
network_code,
0 as type1,
0 as type2,
1 as type3,
0 as type4
FROM
t_network
where datediff('{{ execution_date | cst_ds }}',opening_time) < 15
and datediff('{{ execution_date | cst_ds }}',opening_time) >= 0
) t group by network_code
) f left join t_network b on f.network_code=b.network_code
),
old_data as(
select * from
jms_dim.dim_network_abnormal_type_union_dt_hi
where dt='{{ execution_date | date_add(-1) | cst_ds }}'
),
repair_data as(
select
t1.*,
'1' as is_real_delete
from old_data t1
left join new_data t2
on t1.network_code=t2.network_code
where t2.network_code is null)



insert overwrite table jms_dim.dim_network_abnormal_type_union_dt_hi partition(dt)
select * from
(
SELECT
input_date,
network_code,
network_name,
regional_id,
regional_desc,
area_info_id,
area_info_desc,
provider_id,
provider_desc,
city_id,
city_desc,
area_id,
area_desc,
is_shaidan,
is_delay,
is_new_open,
is_special_report,
create_time,
is_real_delete as is_delete,
'{{ execution_date | cst_ds }}' as dt
from
repair_data
union all
SELECT
input_date,
network_code,
network_name,
regional_id,
regional_desc,
area_info_id,
area_info_desc,
provider_id,
provider_desc,
city_id,
city_desc,
area_id,
area_desc,
is_shaidan,
is_delay,
is_new_open,
is_special_report,
create_time,
'0'  as is_delete,
'{{ execution_date | cst_ds }}' as dt
from new_data
);